package com.fmi.timedealer.datamanager;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.fmi.timedealer.Category;
import com.fmi.timedealer.ActivityItem;
import com.fmi.timedealer.achievement.Achievement;
import com.fmi.timedealer.achievement.AchievementLevel;
import com.fmi.timedealer.achievement.AchievementLevelItem;
import com.fmi.timedealer.moodtracking.Mood;
import com.fmi.timedealer.moodtracking.TrackedMood;
import com.fmi.timedealer.plan.Plan;
import com.fmi.timedealer.plan.PlannedActivity;
import com.fmi.timedealer.reporting.model.ActivitiesPerCategoryReportItem;
import com.fmi.timedealer.reporting.model.CategoriesReportItem;
import com.fmi.timedealer.reporting.model.CustomReportItem;
import com.fmi.timedealer.reporting.model.MoodReportItem;
import com.fmi.timedealer.track.TrackLog;

public class SQLiteDataManager implements IDataManager {
	
	private SQLiteDatabase generatorDB = null;
	private Context context;
	private String databaseName;
	
	//#TODO move to global variables
	private static final String ACTIVITY_ITEMS_TABLE = "TBL_TIMEACTIONS";
	private static final String PLAN_TABLE = "TBL_PLANS";
	private static final String PLANNED_ACTIVITY_TABLE = "TBL_PLANNED_ACTIVITIES";
	private static final String TRACK_TABLE = "TBL_TRACK";
	private static final String CATEGORY_TABLE = "TBL_CATEGORY";
	private static final String ACHIEVEMENTS_TABLE = "TBL_ACHIEVEMENTS";
	private static final String LEVELS_TABLE = "TBL_LEVELS";
	private static final String LEVEL_MAPPING_TABLE = "TBL_LEVEL_MAP";
	private static final String LEVEL_ITEMS_TABLE = "TBL_LEVEL_ITEMS";
	private static final String LEVEL_ITEM_MAPPING_TABLE = "TBL_LEVEL_ITEM_MAP";
	private static final String MOOD_TABLE = "TBL_MOOD";
	//private static final String TRACK_TABLE2 = "TBL_TRACK_2";

	public SQLiteDataManager(String dbName, Activity currentActivity) {
		this(dbName, currentActivity.getApplicationContext());
	}
	
	public SQLiteDataManager(String dbName, Context context) {
		this.databaseName = dbName;
		this.context = context;
		initTables();
		initValues();
	}
	
	/**
	 * Establish connection to the database (opens or creates the database)
	 */
	private void openConnection() throws SQLException{
		try {
			if (generatorDB == null) {
				generatorDB = context.openOrCreateDatabase(databaseName, Activity.MODE_PRIVATE, null);
				Log.d(getClass().getSimpleName(), "Established connection to database " + databaseName);
			}
		} catch(SQLException e) {
			Log.e(getClass().getSimpleName(), "Connection to database " + databaseName + 
					" cannot be established.", e);
			throw e;
		}
	}
	
	/**
	 * Close the current connection if it was already established
	 */
	private void closeConnection() throws SQLException {
		if (generatorDB != null) {
			try {
				generatorDB.close();
				generatorDB = null;
				Log.d(getClass().getSimpleName(), "Connection to the database " + databaseName + " has been closed.");
			} catch(SQLException e) {
				Log.e(getClass().getSimpleName(), "Connection to database " + databaseName + 
						" cannot be closed.", e);
				throw e;
			}
		}
	}
	
	//use this to delete your data
	@SuppressWarnings("unused")
	private void emptyDBRecords(String[] tables) {
		for (String table : tables) {
			generatorDB.execSQL("DELETE FROM " + table);
		}
	}
	
	/*private void dropTables(String[] tables) {
		for (String table : tables) {
			generatorDB.execSQL("DROP TABLE IF EXISTS " + table + ";");
		}
	}*/
	
	private void initValues() {
		try {
			openConnection();
			
			Cursor c1 = generatorDB.rawQuery("SELECT * FROM " + CATEGORY_TABLE, null);
			
			if(c1.getCount() == 0) {
				insertCategory(new Category("Sport"));
				insertCategory(new Category("Party"));
				insertCategory(new Category("University"));
				insertCategory(new Category("Work"));
				insertCategory(new Category("Others"));
				openConnection();
			}
			c1.close();
			
			Cursor c = generatorDB.rawQuery("SELECT * FROM " + ACTIVITY_ITEMS_TABLE, null);
				
			if (c.getCount() == 0) {
				List<ActivityItem> actions = new ArrayList<ActivityItem>();
				
				actions.add(new ActivityItem("Play football", "Kicking balls", "Sport"));
				actions.add(new ActivityItem("Swimming", "Swimming", "Sport"));
				actions.add(new ActivityItem("Play tennis", "Play tennis", "Sport"));
				actions.add(new ActivityItem("Study", "yeah right, study", "University"));	
				actions.add(new ActivityItem("Homework", "Do homework", "University"));
				actions.add(new ActivityItem("Lectures", "Stay in class", "University"));
				actions.add(new ActivityItem("Do project", "Do course project", "University"));
				actions.add(new ActivityItem("Karaoke", "Only for deaf people", "Party"));
				actions.add(new ActivityItem("Walk in the park", "Walk in the park", "Party"));
				actions.add(new ActivityItem("Go to disco", "Go to disco", "Party"));
				actions.add(new ActivityItem("Clean", "Clean house", "Others"));
				actions.add(new ActivityItem("Cooking", "Cook dinner", "Others"));
				actions.add(new ActivityItem("Project X", "Totally secret", "Work"));
				actions.add(new ActivityItem("Stay in the office", "Work", "Work"));

				for(ActivityItem action: actions) {
					recordActivityItem(action);
				}
				openConnection();
			}
			c.close();
			
			Cursor c3 = generatorDB.rawQuery("SELECT * FROM " + TRACK_TABLE,
					null);

			if (c3.getColumnCount() == 4) {

				generatorDB
						.execSQL("ALTER TABLE TBL_TRACK ADD TIME_SPENT INTEGER;");
				updateTrackRows();
			}
			c3.close();
		} finally {
			closeConnection();
		}
	}
	
	private void initTables() {
		try {
			openConnection();
			
			//dropTables(new String[] { ACHIEVEMENTS_TABLE });
			//emptyDBRecords(new String[] { LEVEL_ITEM_MAPPING_TABLE, LEVEL_ITEMS_TABLE, LEVEL_MAPPING_TABLE, LEVELS_TABLE });
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + ACTIVITY_ITEMS_TABLE + 
					" (id integer primary key autoincrement, NAME VARCHAR, DESCRIPTION VARCHAR, CATEGORY VARCHAR);");
		
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + PLAN_TABLE + 
					" (id integer primary key autoincrement, NAME VARCHAR, DESCRIPTION VARCHAR, START DATE, END DATE);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + PLANNED_ACTIVITY_TABLE + 
					" (id integer primary key autoincrement, PLAN_ID integer, ACTIVITY_ID integer, DURATION DOUBLE);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + TRACK_TABLE + 
					" (id INTEGER primary key autoincrement, TIMEACTION_ID INTEGER, START DATE, END DATE, TIME_SPENT INTEGER);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + CATEGORY_TABLE + 
					" (id INTEGER primary key autoincrement, NAME VARCHAR);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + ACHIEVEMENTS_TABLE +
					" (id INTEGER primary key autoincrement, NAME VARCHAR, GOAL_MESSAGE VARCHAR, IS_COMPLETED INTEGER);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + LEVELS_TABLE + 
					" (id INTEGER primary key autoincrement, NAME VARCHAR, LEVEL INTEGER, GOAL_MESSAGE VARCHAR, IS_COMPLETED INTEGER);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + LEVEL_MAPPING_TABLE +
					" (id INTEGER primary key autoincrement, ACHIEVEMENT_ID INTEGER, LEVEL_ID INTEGER);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + LEVEL_ITEMS_TABLE + 
					" (id INTEGER primary key autoincrement, ACTIVITY_ID INTEGER, GOAL INTEGER, IS_COMPLETED INTEGER);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + LEVEL_ITEM_MAPPING_TABLE + 
					" (id INTEGER primary key autoincrement, LEVEL_ITEM_ID INTEGER, LEVEL_ID INTEGER);");
			
			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + MOOD_TABLE +
					" (id INTEGER primary key autoincrement, MOOD_CODE INTEGER, START DATE, END DATE);");
			
//			generatorDB.execSQL("CREATE TABLE IF NOT EXISTS " + TRACK_TABLE2 +
//					" (id INTEGER primary key autoincrement, TIMEACTION_ID INTEGER, START DATE, END DATE, SPENT_TIME INTEGER);");
						
			
		} finally {
			closeConnection();
		}
	}
	
	// fill TIME_SPENT column, has been added
	private boolean updateTrackRow(TrackLog log) {	
		try {
    		openConnection();
    		ContentValues values = getValuesFromTrackLog(log);
    		int timeSpent = values.getAsInteger("TIME_SPENT");
    		String endDate = values.getAsString("END");
    		String startDate = values.getAsString("START");
    		
    		if(timeSpent == 0 && endDate != null) {
    			timeSpent = (int) (((new Date(endDate)).getTime() - (new Date(startDate)).getTime())/60000);
    			values.put("TIME_SPENT", timeSpent);
    		}
    		generatorDB.update(TRACK_TABLE, values, "id=?", 
    				new String[]{Long.toString(log.getId())});
    	} catch(Exception e) {
    		e.printStackTrace();
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public List<TrackLog> getAllTracks() {
		
		ArrayList<TrackLog> items = new ArrayList<TrackLog>();
		long activityItemId;
		String start = "";
		String end = "";
		int timeSpent;
		
		try {
			openConnection();
			String query = "SELECT * FROM " + TRACK_TABLE;
			
			Cursor c = generatorDB.rawQuery(query, null);
			
			int activityIDColumn = c.getColumnIndex("TIMEACTION_ID");
			int startColumn = c.getColumnIndex("START");
			int endColumn = c.getColumnIndex("END");
			int timeSpentColumn = c.getColumnIndex("TIME_SPENT");
			
			if (c.moveToFirst()) {
				do {
					activityItemId = c.getInt(activityIDColumn);
					start = c.getString(startColumn);
					end = c.getString(endColumn);
					Date s = new Date(start);
					Date e = end != null ? new Date(end) : null;
					timeSpent = c.getInt(timeSpentColumn);
					TrackLog log = new TrackLog(activityItemId, s, e);
					log.setId(c.getLong(0));
					log.setTimeSpent(timeSpent);
					items.add(log);
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return items;
	}

	private ContentValues getValuesFromActivityItem(ActivityItem action) {
		ContentValues values = new ContentValues();
		values.put("NAME", action.getName());
		values.put("CATEGORY", action.getCategory());
		values.put("DESCRIPTION", action.getDescription());
		return values;
	}
	
	private ContentValues getValuesFromPlan(Plan plan) {
		ContentValues values = new ContentValues();
		values.put("NAME", plan.getName());
		values.put("DESCRIPTION", plan.getDescription());
		values.put("START", plan.getStart().toString());
		values.put("END", plan.getEnd().toString());
		return values;
	}
	
	private ContentValues getValuesFromPlannedActivity(PlannedActivity item) {
		ContentValues values = new ContentValues();
		values.put("PLAN_ID", item.getPlanId());
		values.put("ACTIVITY_ID", item.getActivityItem().getId());
		values.put("DURATION", item.getDuration());
		return values;
	}
	
	private ContentValues getValuesFromTrackLog(TrackLog log) {
		ContentValues values = new ContentValues();
		values.put("TIMEACTION_ID", log.getActivityId());
		values.put("START", log.getStartDate().toString());
		Date end = log.getEndDate();
		if (end != null) {
			values.put("END", end.toString());
		}
		values.put("TIME_SPENT", log.getTimeSpent());
		return values;
	}
	
//	private ContentValues getValuesFromTrackLog2(TrackLog2 log) {
//		ContentValues values = new ContentValues();
//		values.put("TIMEACTION_ID", log.getActivityId());
//		values.put("START", log.getStartDate().toString());
//		Date end = log.getEndDate();
//		if (end != null) {
//			values.put("END", end.toString());
//		}
//		values.put("SPENT_TIME", log.getTimeSpent());
//		return values;
//	}
	
	private ContentValues getValuesFromCategory(Category category) {
		ContentValues values = new ContentValues();
		values.put("NAME", category.getName());
		return values;
	}
	
	private ContentValues getValuesForCurrentDate(String key) {
		ContentValues values = new ContentValues();
		values.put(key, (new Date()).toString());
		return values;
	}
	
	private ContentValues getValuesForCurrentMood(String key) {
		ContentValues values = new ContentValues();
		values.put(key, (new Date()).toString());
		return values;
	}
	
	private ContentValues getValuesFromAchievement(Achievement achievement) {
		ContentValues values = new ContentValues();
		values.put("NAME", achievement.getName());
		values.put("GOAL_MESSAGE", achievement.getGoalMessage());
		int completed = 0;
		if (achievement.isCompleted()) {
			completed = 1;
		}
		values.put("IS_COMPLETED", completed);
		return values;
	}
	
	private ContentValues getValuesFromLevel(AchievementLevel level) {
		ContentValues values = new ContentValues();
		values.put("NAME", level.getName());
		values.put("GOAL_MESSAGE", level.getGoalMessage());
		values.put("LEVEL", level.getLevel());
		int completed = 0;
		if (level.isCompleted()) {
			completed = 1;
		}
		values.put("IS_COMPLETED", completed);
		return values;
	}
	
	private ContentValues getValuesFromLevelItem(AchievementLevelItem item, boolean update) {
		ContentValues values = new ContentValues();
		values.put("ACTIVITY_ID", item.getActivityItem().getId());
		values.put("GOAL", item.getGoalTrackTime());
		int isCompleted = 0;
		if (update) {
			boolean wasCompleted = item.isCompleted();
			item.updateRemaining(getTimeSpentForActivity(item.getActivityItem().getId()));
			if (item.isCompleted() && wasCompleted) {
				isCompleted = 1;
			} else if (!item.isCompleted() && wasCompleted) {
				AchievementLevel level = getLevelsByLevelItems(Arrays.asList(item)).get(0);
				level.setCompleted(false);
				updateLevel(level);
			}
		} else {
			item.updateRemaining(getTimeSpentForActivity(item.getActivityItem().getId()));
		}
		values.put("IS_COMPLETED", isCompleted);
		return values;
	}
	private ContentValues getValuesFromMood(TrackedMood trackedMood) {
		ContentValues values = new ContentValues();
		values.put("MOOD_CODE", trackedMood.getMoodCode());
		values.put("START", trackedMood.getStartDate().toString());
		if(trackedMood.getEndDate() != null) {
			values.put("END", trackedMood.getEndDate().toString());
		}
		return values;
	}

	public boolean recordActivityItem(ActivityItem activityItem) throws SQLException {
		try {
			openConnection();
			generatorDB.insertOrThrow(ACTIVITY_ITEMS_TABLE, null, getValuesFromActivityItem(activityItem));
			Log.d(getClass().getSimpleName(), "Successfully inserted (" +activityItem.getName() + ", " +
					activityItem.getCategory() + ", " + activityItem.getDescription() + ") into table " + ACTIVITY_ITEMS_TABLE);
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Error while insert in table " + ACTIVITY_ITEMS_TABLE);
			return false;
		} finally {
			closeConnection();
		}
		
		return true;
	}

	public boolean updateActivityItem(ActivityItem activityItem) throws SQLException {
    	try {
    		openConnection();
    		generatorDB.update(ACTIVITY_ITEMS_TABLE, getValuesFromActivityItem(activityItem), "id=?", 
    				new String[]{Integer.toString(activityItem.getId())});
    		Log.d(getClass().getSimpleName(), "Successfully updated to (" + activityItem.getCategory() 
    				+ ", " + activityItem.getDescription() + ") in table " + ACTIVITY_ITEMS_TABLE);
    	} catch(Exception e) {
    		e.printStackTrace();
    		Log.e(getClass().getSimpleName(), "Cannot update to (" + activityItem.getCategory() 
    				+ ", " + activityItem.getDescription() + ") in table " + ACTIVITY_ITEMS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public boolean deleteActivityItem(ActivityItem activityItem) throws SQLException {
    	try {
    		openConnection();
    		generatorDB.delete(ACTIVITY_ITEMS_TABLE, "id=?", 
    				new String[]{Integer.toString(activityItem.getId())});
    		Log.d(getClass().getSimpleName(), "Successfully deleted (" + activityItem.getCategory() 
    				+ ", " + activityItem.getDescription() + ") from table " + ACTIVITY_ITEMS_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot delete (" + activityItem.getCategory() 
    				+ ", " + activityItem.getDescription() + ") from table " + ACTIVITY_ITEMS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public String[] getCategoriesNames() {
		
		String[] categoriesNames = new String[getCategoriesCount()];
		try {
    		openConnection();
    		
			String query = "SELECT * FROM " + CATEGORY_TABLE;

			Cursor c = generatorDB.rawQuery(query, null);
			
			int nameColumn = c.getColumnIndex("NAME");
			String name = null;
			int index = 0;
			
			if (c.moveToFirst()) {
				do {
					name = c.getString(nameColumn);
					categoriesNames[index] = name;
					index++;
				} while (c.moveToNext());
			}
			c.close();
    	} catch(Exception e) {
    		e.printStackTrace();
    	} finally {
    		closeConnection();
    	}
		return categoriesNames;
	}

	public boolean insertPlan(Plan plan) throws SQLException {
		try {
			openConnection();
			int id = (int) generatorDB.insertOrThrow(PLAN_TABLE, null, getValuesFromPlan(plan));
			plan.setId(id);
			Log.d(getClass().getSimpleName(), "Successfully inserted (" + plan.getName() + ", " +
					plan.getDescription() + ") into table " + PLAN_TABLE);
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Error while insert in table " + PLAN_TABLE);
			return false;
		} finally {
			closeConnection();
		}
		
		return true;
	}
	
	public ArrayList<Plan> getPlans() throws SQLException {
		
		ArrayList<Plan> result = new ArrayList<Plan>();
		int id;
		String description = "";
		String name = "";
		Date start;
		Date end;
		
		try {
			openConnection();
			
			Cursor c = generatorDB.rawQuery("SELECT * FROM " + PLAN_TABLE, null);
			
			int idColumn = c.getColumnIndex("id");
			int nameColumn = c.getColumnIndex("NAME");
			int descColumn = c.getColumnIndex("DESCRIPTION");
			int startColumn = c.getColumnIndex("START");
			int endColumn = c.getColumnIndex("END");
			
			if (c.moveToFirst()) {
				do {
					id = c.getInt(idColumn);
					name = c.getString(nameColumn);
					description = c.getString(descColumn);
					start = new Date(c.getString(startColumn));
					end = new Date(c.getString(endColumn));
					
					result.add(new Plan(id, name, description, start, end));
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + PLAN_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		
		return result;
	}
	
	public boolean updatePlan(Plan plan) throws SQLException {
		
		try {
    		openConnection();
    		generatorDB.update(PLAN_TABLE, getValuesFromPlan(plan), "id=?", 
    				new String[]{ Integer.toString(plan.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully updated to (" + plan.getName() 
    				+ ", " + plan.getDescription() + ") in table " + PLAN_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot update to (" + plan.getName() 
    				+ ", " + plan.getDescription() + ") in table " + PLAN_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}
	
	public boolean deletePlan(Plan plan) throws SQLException {
    	try {
    		openConnection();
    		generatorDB.delete(PLANNED_ACTIVITY_TABLE, "PLAN_ID=?", 
    				new String[]{ Integer.toString(plan.getId()) });
    		generatorDB.delete(PLAN_TABLE, "id=?", 
    				new String[]{ Integer.toString(plan.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully deleted (" + plan.getName() 
    				+ ", " + plan.getDescription() + ") from table " + PLAN_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot delete (" + plan.getName() 
    				+ ", " + plan.getDescription() + ") from table " + PLAN_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}
	
	public boolean isActivityRunning(ActivityItem activity) {
		boolean isRunning = false;
		try {
			openConnection();
			Cursor c = generatorDB.rawQuery("SELECT * FROM " + TRACK_TABLE 
						+ " WHERE END IS NULL AND TIMEACTION_ID = " + activity.getId(), null);
			isRunning = c.moveToFirst();
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot check if activity " + activity.getName() + " is running." + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return isRunning;
	}
	
	public boolean trackTime(ActivityItem activity, Date startDate, Date endDate) {
		
		boolean success = false;
		try {
			openConnection();
			long activityId = getTimeActionId(activity);
			if (activityId == -1) {
				Log.e(getClass().getSimpleName(),"Cannot find such activity");
			} else {
				TrackLog log = new TrackLog(activityId, startDate, endDate);
				ContentValues values = getValuesFromTrackLog(log);
	    		int timeSpent = 0;
	    		if(endDate != null) {
	    			timeSpent = (int) ((endDate.getTime() - (startDate).getTime())/60000);
	    		}
    			values.put("TIME_SPENT", timeSpent);

				generatorDB.insertOrThrow(TRACK_TABLE, null, values);
				Log.d(getClass().getSimpleName(), "Successfully inserted (" + activity.getName() + ", " +
						startDate + ", " + endDate + ") into table " + TRACK_TABLE);
				success = true;
			}
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot track time: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return success;
	}
	
//	public boolean trackTime2(long activityId, Date startDate, Date endDate) {
//		
//		boolean success = false;
//		try {
//			openConnection();
//			int spenttime = 0;
//			if(endDate == null) {
//				spenttime = (int) (((new Date().getTime()) - startDate.getTime())/60000);
//			}
//			else {
//				spenttime = (int) (((endDate.getTime()) - startDate.getTime())/60000);
//			}
//			if (activityId == -1) {
//				Log.e(getClass().getSimpleName(),"Cannot find such activity");
//			} else {
//				TrackLog2 log = new TrackLog2(activityId, startDate, endDate, spenttime);
//				
//				generatorDB.insertOrThrow(TRACK_TABLE2, null, getValuesFromTrackLog2(log));
//				
//				success = true;
//			}
//		} catch (SQLException e) {
//			Log.e(getClass().getSimpleName(), "Cannot track time: " + e.getMessage(), e);
//		} finally {
//			closeConnection();
//		}
//		return success;
//	}

	private long getTimeActionId(ActivityItem activity) {
		long id = -1;
		String query = "SELECT * FROM " + ACTIVITY_ITEMS_TABLE + " WHERE NAME = ? AND CATEGORY = ?";
		Cursor c = generatorDB.rawQuery(query, new String[]{activity.getName(), activity.getCategory()});
		
		int idColumn = c.getColumnIndex("id");

		if (c.moveToFirst()) {
			id = c.getLong(idColumn);
		}
		c.close();
		return id;
	}
	
	public ArrayList<TrackLog> getActivities(boolean onlyActive) throws SQLException {

		ArrayList<TrackLog> logs = new ArrayList<TrackLog>();
		long activityId = -1;
		Date start = null;
		Date end = null;
		try {
			openConnection();
			String query = "SELECT * FROM " + TRACK_TABLE;
			if (onlyActive) {
				query += " WHERE END IS NULL";
			}
			Cursor c = generatorDB.rawQuery(query, null);
			
			int activityIdColumn = c.getColumnIndex("TIMEACTION_ID");
			int startColumn = c.getColumnIndex("START");
			int endColumn = c.getColumnIndex("END");
			
			if (c.moveToFirst()) {
				do {
					activityId = c.getInt(activityIdColumn);
					start = new Date(c.getString(startColumn));
					String endDateStr = c.getString(endColumn);
					if (endDateStr != null) {
						end = new Date(endDateStr);
					} else {
						end = null;
					}
					logs.add(new TrackLog(activityId, start, end));
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return logs;
	}

	public HashMap<Long, ActivityItem> getActivitiesByIds(long[] activityIds) {

		HashMap<Long, ActivityItem> items = new HashMap<Long, ActivityItem>();
		String name = "";
		String category = "";
		String description = "";
		try {
			openConnection();
			String query = "SELECT * FROM " + ACTIVITY_ITEMS_TABLE + " WHERE ID IN " + buildInClauseForIds(activityIds);
			
			Cursor c = generatorDB.rawQuery(query, null);
			
			int nameColumn = c.getColumnIndex("NAME");
			int categoryColumn = c.getColumnIndex("CATEGORY");
			int descColumn = c.getColumnIndex("DESCRIPTION");
			int idColumn = c.getColumnIndex("id");
			
			if (c.moveToFirst()) {
				do {
					name = c.getString(nameColumn);
					category = c.getString(categoryColumn);
					description = c.getString(descColumn);
					items.put(c.getLong(idColumn), new ActivityItem(c.getInt(idColumn), name, description, category));
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return items;
	}

	private String buildInClauseForIds(long[] values) {
		
		String inClause = "(";
		
		for (int i = 0; i < values.length - 1; i++) {
			inClause += values[i] + ",";
		}
		if (values.length > 0) {
			inClause += values[values.length - 1];
		}
		inClause += ")";
		return inClause;
	}

	public boolean stopTracking(ActivityItem activity) {
		boolean success = true;
		try {
    		openConnection();
    		long activityId = getTimeActionId(activity);
    		if (activityId != -1) {
	    		int count = generatorDB.update(TRACK_TABLE, getValuesForCurrentDate("END"), "TIMEACTION_ID = ?", 
	    				new String[]{Long.toString(activityId)});
	    		if (count == 0) {
	    			success = false;
	    		}
	    		Log.d(getClass().getSimpleName(), "Successfully updated " + count + " rows with (" + activity.getCategory() 
		    				+ ", " + activity.getDescription() + ") in table " + TRACK_TABLE);
    		}
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot update row with (" + activity.getCategory() 
    				+ ", " + activity.getDescription() + ") in table " + TRACK_TABLE, e);
    		success = false;
    	} finally {
    		closeConnection();
    	}
    	return success;
	}
	
	public List<CustomReportItem> getReport(String category, Date start, Date end) {
		
		List<CustomReportItem> reportItems = new ArrayList<CustomReportItem>();
		
		try {
			openConnection();
			
			String criteria = "";
			
			if (category != null && !category.equals("")) {
				criteria += " WHERE CATEGORY='" + category + "'";	
			}
			
			String allActivitiesOfCategory = "SELECT NAME, CATEGORY, START, END, TIME_SPENT FROM " + TRACK_TABLE + 
					" JOIN  " + ACTIVITY_ITEMS_TABLE + " ON TBL_TRACK.TIMEACTION_ID=TBL_TIMEACTIONS.id" + criteria + ";";
			
			Cursor c = generatorDB.rawQuery(allActivitiesOfCategory, null);
			
			
			String activityName = null;
			String activityCategory = null;
			Date endDate = null;
			Date startDate = null;
						
			int activityNameColumn = c.getColumnIndex("NAME");
			int activityCategoryColumn = c.getColumnIndex("CATEGORY");
			int startColumn = c.getColumnIndex("START");
			int endColumn = c.getColumnIndex("END");
			
			if (c.moveToFirst()) {
				do {
					activityName = c.getString(activityNameColumn);
					activityCategory = c.getString(activityCategoryColumn);
					startDate = new Date(c.getString(startColumn));
					String endDateStr = c.getString(endColumn);
					if (endDateStr != null) {
						endDate = new Date(endDateStr);
					} else {
						endDate = null;
					}
					
					if (endDate != null) {
						int timeInMinutes = c.getInt(4);
						int hours = timeInMinutes/60;
						int minutes = timeInMinutes % 60;
						String timeSpentInHours = null;
						if(hours == 0) {
							timeSpentInHours = minutes + " min";
						}
						else {
							timeSpentInHours = hours + " h, " + minutes + " min";
						}
						
						if(start == null) {
							reportItems.add(new CustomReportItem(activityName, activityCategory, timeSpentInHours, startDate));
						}
						else { // filter by date
							if(startDate.compareTo(start) >= 0 && endDate.compareTo(end) <= 0) {
								reportItems.add(new CustomReportItem(activityName, activityCategory, timeSpentInHours, startDate));
							}
						}
					}
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		
		return reportItems;
	}
	
	public ArrayList<PlannedActivity> getPlannedActivities(int planId) {

		ArrayList<PlannedActivity> result = new ArrayList<PlannedActivity>();
		
		int id;
		int activityId;
		String name = "";
		String category = "";
		float duration;
		
		try {
			openConnection();
			
			Cursor c = generatorDB.rawQuery("SELECT p.id, ACTIVITY_ID, NAME, CATEGORY, DURATION FROM " + PLANNED_ACTIVITY_TABLE + " as p " +
											"JOIN " + ACTIVITY_ITEMS_TABLE + " as ta ON ta.id == p.ACTIVITY_ID " +
											"WHERE p.PLAN_ID == " + Integer.toString(planId), null);
			
			int idColumn = c.getColumnIndex("id");
			int activityIdColumn = c.getColumnIndex("ACTIVITY_ID");
			int nameColumn = c.getColumnIndex("NAME");
			int categoryColumn = c.getColumnIndex("CATEGORY");
			int durationColumn = c.getColumnIndex("DURATION");
			
			if (c.moveToFirst()) {
				do {
					id = c.getInt(idColumn);
					activityId = c.getInt(activityIdColumn);
					name = c.getString(nameColumn);
					category = c.getString(categoryColumn);
					duration = c.getFloat(durationColumn);
					
					result.add(new PlannedActivity(id, planId, duration, new ActivityItem(activityId, name, "", category)));
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + PLAN_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		
		return result;
	}
	
	public boolean insertPlannedActivity(PlannedActivity plannedActivity) throws SQLException {
		try {
			openConnection();
			int id = (int) generatorDB.insertOrThrow(PLANNED_ACTIVITY_TABLE, null, getValuesFromPlannedActivity(plannedActivity));
			plannedActivity.setId(id);
			Log.d(getClass().getSimpleName(), "Successfully inserted (" + plannedActivity.getPlanId() + ", " +
					plannedActivity.getActivityItem().getId() + ") into table " + PLANNED_ACTIVITY_TABLE);
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Error while insert in table " + PLANNED_ACTIVITY_TABLE);
			return false;
		} finally {
			closeConnection();
		}
		
		return true;
	}

	public boolean deletePlannedActivity(PlannedActivity plannedActivity) throws SQLException {

		try {
    		openConnection();
    		generatorDB.delete(PLANNED_ACTIVITY_TABLE, "id=?", 
    				new String[]{ Integer.toString(plannedActivity.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully deleted (" + plannedActivity.getPlanId() 
    				+ ", " + plannedActivity.getActivityItem().getId() + ") from table " + PLANNED_ACTIVITY_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot delete (" + plannedActivity.getPlanId() 
    				+ ", " + plannedActivity.getActivityItem().getId() + ") from table " + PLANNED_ACTIVITY_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}
	
	public boolean updatePlannedActivity(PlannedActivity plannedActivity) throws SQLException {
		
		try {
    		openConnection();
    		generatorDB.update(PLANNED_ACTIVITY_TABLE, getValuesFromPlannedActivity(plannedActivity), "id=?", 
    				new String[]{ Integer.toString(plannedActivity.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully updated to (" + plannedActivity.getActivityItem().getName() 
    				+ ", " + plannedActivity.getActivityItem().getDescription() + ") in table " + PLANNED_ACTIVITY_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot update to (" + plannedActivity.getActivityItem().getName() 
    				+ ", " + plannedActivity.getActivityItem().getDescription() + ") in table " + PLANNED_ACTIVITY_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public ArrayList<ActivityItem> getAllActivities() throws SQLException {

		ArrayList<ActivityItem> items = new ArrayList<ActivityItem>();
		int id;
		String name = "";
		String category = "";
		String description = "";
		try {
			openConnection();
			String query = "SELECT * FROM " + ACTIVITY_ITEMS_TABLE;
			
			Cursor c = generatorDB.rawQuery(query, null);
			
			int nameColumn = c.getColumnIndex("NAME");
			int categoryColumn = c.getColumnIndex("CATEGORY");
			int descColumn = c.getColumnIndex("DESCRIPTION");
			int idColumn = c.getColumnIndex("id");
			
			if (c.moveToFirst()) {
				do {
					name = c.getString(nameColumn);
					category = c.getString(categoryColumn);
					description = c.getString(descColumn);
					id = c.getInt(idColumn);
					items.add(new ActivityItem(id, name, description, category));
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return items;
	}

	// TODO
	public List<Category> getAllCategories() {
		
		List<Category> categories = new ArrayList<Category>();
		try {
    		openConnection();
    		
			String query = "SELECT * FROM " + CATEGORY_TABLE;

			Cursor c = generatorDB.rawQuery(query, null);
			
			int nameColumn = c.getColumnIndex("NAME");
			String name = null;
			int id = -1;
			
			if (c.moveToFirst()) {
				do {
					name = c.getString(nameColumn);
					id = c.getInt(0);
					Category category = new Category(name);
					category.setId(id);
					categories.add(category);
				} while (c.moveToNext());
			}
			c.close();
    	} catch(Exception e) {
    		e.printStackTrace();
    	} finally {
    		closeConnection();
    	}
		return categories;
	}

	// TODO
	public List<ActivityItem> getActivitiesForCategory(String category) {
		
		List<ActivityItem> result = new ArrayList<ActivityItem>();
		
		for(ActivityItem item : getAllActivities()) {
			if(item.getCategory().equals(category)) {
				result.add(item);
			}
		}
		
		return result;
	}

	public boolean insertCategory(Category category) {
		try {
			openConnection();
			int id = (int) generatorDB.insertOrThrow(CATEGORY_TABLE, null, getValuesFromCategory(category));
			category.setId(id);
			Log.d(getClass().getSimpleName(), "Successfully inserted (" + category.getId() + ", " +
					category.getName() + ") into table " + CATEGORY_TABLE);
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Error while insert in table " + CATEGORY_TABLE);
			return false;
		} finally {
			closeConnection();
		}
		
		return true;
	}

	public boolean deleteCategory(Category category) {
		
		try {
    		openConnection();
    		
    		generatorDB.delete(CATEGORY_TABLE, "id=?", 
    				new String[]{ Integer.toString(category.getId()) });
    		String query = "DELETE FROM " + ACTIVITY_ITEMS_TABLE + 
    				" WHERE CATEGORY='" + category.getName() + "';";
    		generatorDB.execSQL(query);
    		Log.d(getClass().getSimpleName(), "Successfully deleted (" + category.getId() 
    				+ ", " + category.getName() + ") from table " + CATEGORY_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot delete (" + category.getId()  
    				+ ", " + category.getName() + ") from table " + CATEGORY_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public boolean updateCategory(Category category, String oldCategoryName) {
		try {
    		openConnection();
    		generatorDB.update(CATEGORY_TABLE, getValuesFromCategory(category), "id=?", 
    				new String[]{Integer.toString(category.getId())});
    		
    		String query = "UPDATE " + ACTIVITY_ITEMS_TABLE + 
    			" SET CATEGORY='" + category.getName() + "' WHERE CATEGORY='" + oldCategoryName + "';";  
    	
    		generatorDB.execSQL(query);
    		Log.d(getClass().getSimpleName(), "Successfully updated to (" + category.getName() + ") in table " + CATEGORY_TABLE);
    	} catch(Exception e) {
    		e.printStackTrace();
    		Log.e(getClass().getSimpleName(), "Cannot update to (" +  category.getName() + ") in table " + CATEGORY_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	private int getCategoriesCount() {
		
		int categoriesCount = 0;
		try {
    		openConnection();
    		
			String query = "SELECT COUNT(*) FROM " + CATEGORY_TABLE;

			Cursor c = generatorDB.rawQuery(query, null);
			
			if (c.moveToFirst()) {
				do {
					categoriesCount = c.getInt(0);
				} while (c.moveToNext());
			}
			c.close();
    	} catch(Exception e) {
    		e.printStackTrace();
    	} finally {
    		closeConnection();
    	}
		return categoriesCount;
	}

	/**
	 * returns time in minutes that is spent on current  activity 
	 */
	public int getTimeSpentForActivity(int activityId) {
		
		
		int minutes = 0;
		
		try {
			String query = "SELECT TBL_TRACK.TIMEACTION_ID, TBL_TRACK.START, TBL_TRACK.END, TBL_TRACK.TIME_SPENT  FROM " + TRACK_TABLE +
					"  WHERE TBL_TRACK.TIMEACTION_ID=" + activityId;

			Cursor c = generatorDB.rawQuery(query, null);
			if (c.moveToFirst()) {
				do {			
					Date startDate = new Date(c.getString(1));
					String endDateStr = c.getString(2);
					if(endDateStr != null) {
						minutes += c.getInt(3);
					}
					else {
						minutes += (int) (((new Date()).getTime() - startDate.getTime())/60000);
					}
										
				} while (c.moveToNext());
			}
			c.close();
    	} catch(Exception e) {
    		e.printStackTrace();
    	}
		return minutes;
	}
	
	/**
	 * returns time in minutes that is spent on current planned actvivity 
	 */
	public int getTimeSpentForPlannedActivity(PlannedActivity activity) {
		
		int minutes = 0;
		
		try {
    		openConnection();
    		
			String query = "SELECT TBL_PLANNED_ACTIVITIES.ACTIVITY_ID, TBL_TRACK.START, TBL_TRACK.END, TBL_PLANS.START, TBL_PLANS.END, TBL_TRACK.TIME_SPENT  FROM " + 
					PLAN_TABLE + " JOIN " + PLANNED_ACTIVITY_TABLE + " on TBL_PLANS.id=TBL_PLANNED_ACTIVITIES.PLAN_ID " + 
					"JOIN " + TRACK_TABLE + " on TBL_PLANNED_ACTIVITIES.ACTIVITY_ID=TBL_TRACK.TIMEACTION_ID WHERE ACTIVITY_ID=" + activity.getActivityItem().getId();

			Cursor c = generatorDB.rawQuery(query, null);
			if (c.moveToFirst()) {
				do {
					
					if(c.getString(2) != null) {
						Date startDate = new Date(c.getString(1));
						Date endDate = new Date(c.getString(2));
						Date startDatePlan = new Date(c.getString(3));
						Date endDatePlan = new Date(c.getString(4));
					
						if(startDate.compareTo(startDatePlan) >= 0 && endDate.compareTo(endDatePlan) <= 0) {
							minutes  += c.getInt(5);
						}
					}
										
				} while (c.moveToNext());
			}
			c.close();
    	} catch(Exception e) {
    		e.printStackTrace();
    	} finally {
    		closeConnection();
    	}
		
		return minutes;
	}

	public boolean insertAchievement(Achievement achievement) {
		try {
			openConnection();
			int id = (int) generatorDB.insertOrThrow(ACHIEVEMENTS_TABLE, null, getValuesFromAchievement(achievement));
			achievement.setId(id);
			Log.d(getClass().getSimpleName(), "Successfully inserted (" + achievement.getName() + ", " +
					achievement.getGoalMessage() + ") into table " + ACHIEVEMENTS_TABLE);
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Error while insert in table " + ACHIEVEMENTS_TABLE);
			return false;
		} finally {
			closeConnection();
		}
		
		return true;
	}

	
	
	public ArrayList<Achievement> getAchievements() {
		ArrayList<Achievement> result = new ArrayList<Achievement>();
		int id;
		String message = "";
		String name = "";
		
		try {
			openConnection();
			
			Cursor c = generatorDB.rawQuery("SELECT * FROM " + ACHIEVEMENTS_TABLE, null);
			
			int idColumn = c.getColumnIndex("id");
			int nameColumn = c.getColumnIndex("NAME");
			int goalColumn = c.getColumnIndex("GOAL_MESSAGE");
			int completedColumn = c.getColumnIndex("IS_COMPLETED");
			
			if (c.moveToFirst()) {
				do {
					id = c.getInt(idColumn);
					name = c.getString(nameColumn);
					message = c.getString(goalColumn);
					boolean isCompleted = false;
					if (c.getInt(completedColumn) == 1) {
						isCompleted = true;
					}
					result.add(new Achievement(id, name, message, isCompleted));
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + ACHIEVEMENTS_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		
		return result;
	}

	public boolean updateAchievement(Achievement achievement) {
		
		try {
    		openConnection();
    		generatorDB.update(ACHIEVEMENTS_TABLE, getValuesFromAchievement(achievement), "id=?", 
    				new String[]{ Integer.toString(achievement.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully updated to (" + achievement.getName() 
    				+ ", " + achievement.getGoalMessage() + ") in table " + ACHIEVEMENTS_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot update to (" + achievement.getName() 
    				+ ", " + achievement.getGoalMessage() + ") in table " + ACHIEVEMENTS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public boolean deleteAchievement(Achievement achievement) {
		try {
    		openConnection();
    		generatorDB.delete(ACHIEVEMENTS_TABLE, "id=?", 
    				new String[]{ Integer.toString(achievement.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully deleted (" + achievement.getName() 
    				+ ", " + achievement.getGoalMessage() + ") from table " + ACHIEVEMENTS_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot delete (" + achievement.getName() 
    				+ ", " + achievement.getGoalMessage() + ") from table " + ACHIEVEMENTS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}
	
	public ArrayList<Integer> getAchievementIdsForLevels(ArrayList<AchievementLevel> levels) {
		ArrayList<Integer> achievementIds = new ArrayList<Integer>();
		
		int achId = 0;
		try {
			openConnection();
			
			String query = "SELECT * FROM " + LEVEL_MAPPING_TABLE + " WHERE LEVEL_ID IN " 
												+ buildInClauseForIds(extractLevelIds(levels));
			
			Cursor c = generatorDB.rawQuery(query, null);
			
			int achIdColumn = c.getColumnIndex("ACHIEVEMENT_ID");
			
			if (c.moveToFirst()) {
				do {
					achId = c.getInt(achIdColumn);
					if (achId != 0 && !achievementIds.contains(achId)) {
						achievementIds.add(achId);
					}
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + LEVEL_MAPPING_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		
		return achievementIds;
	}

	public ArrayList<AchievementLevel> getLevelsForId(int achievementId) {
		ArrayList<AchievementLevel> result = new ArrayList<AchievementLevel>();
		
		int levelId;
		try {
			openConnection();
			
			String query = "SELECT * FROM " + LEVEL_MAPPING_TABLE + " WHERE ACHIEVEMENT_ID = ?";
			
			Cursor c = generatorDB.rawQuery(query, new String[]{ Integer.toString(achievementId) });
			
			int levelIdColumn = c.getColumnIndex("LEVEL_ID");
			
			if (c.moveToFirst()) {
				do {
					levelId = c.getInt(levelIdColumn);
					AchievementLevel level = getLevelById(levelId);
					if (level != null) {
						result.add(level);
					}
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + LEVEL_MAPPING_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		
		return result;
	}
	
	private AchievementLevel getLevelById(int id) {
		AchievementLevel achLevel = null;
		
		String query = "SELECT * FROM " + LEVELS_TABLE + " WHERE id = ?";
		try {
			Cursor c = generatorDB.rawQuery(query, new String[]{ Integer.toString(id) });
			
			int nameColumn = c.getColumnIndex("NAME");
			int levelColumn = c.getColumnIndex("LEVEL");
			int messageColumn = c.getColumnIndex("GOAL_MESSAGE");
			int completedColumn = c.getColumnIndex("IS_COMPLETED");
			
			boolean isCompleted = false;
			if (c.moveToFirst()) {
				do {
					String name = c.getString(nameColumn);
					int level = c.getInt(levelColumn);
					String message = c.getString(messageColumn);
					int completed = c.getInt(completedColumn);
					if (completed == 1) {
						isCompleted = true;
					}
					achLevel = new AchievementLevel(id, level, message, name, isCompleted);
				} while (c.moveToNext());
			}
			c.close();
		} catch (Exception e) {
			Log.e(getClass().getSimpleName(), "Cannot get level for id = " + id, e);
		}
		return achLevel;
	}

	public boolean updateLevel(AchievementLevel level) {
		try {
    		openConnection();
    		generatorDB.update(LEVELS_TABLE, getValuesFromLevel(level), "id=?", 
    				new String[]{ Integer.toString(level.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully updated to (" + level.getLevel() 
    				+ ", " + level.getName() + ") in table " + ACHIEVEMENTS_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot update to (" + level.getLevel() 
    				+ ", " + level.getName() + ") in table " + ACHIEVEMENTS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public boolean deleteLevel(AchievementLevel level, int achievementId) {
		try {
    		openConnection();
    		generatorDB.delete(LEVELS_TABLE, "id=?", 
    				new String[]{ Integer.toString(level.getId()) });
    		generatorDB.delete(LEVEL_MAPPING_TABLE, "ACHIEVEMENT_ID = ? AND LEVEL_ID = ?", 
    				new String[]{ Integer.toString(achievementId), Integer.toString(level.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully deleted (" + level.getLevel() 
    				+ ", " + level.getName() + ") from table " + LEVELS_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot delete (" + level.getLevel() 
    				+ ", " + level.getName() + ") from table " + LEVELS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public boolean insertLevel(AchievementLevel level, int achievementId) {
		try {
			openConnection();
			int id = (int) generatorDB.insertOrThrow(LEVELS_TABLE, null, getValuesFromLevel(level));
			level.setId(id);
			ContentValues values = new ContentValues();
			values.put("ACHIEVEMENT_ID", achievementId);
			values.put("LEVEL_ID", level.getId());
			generatorDB.insertOrThrow(LEVEL_MAPPING_TABLE, null, values);
			Log.d(getClass().getSimpleName(), "Successfully inserted (" + level.getLevel() + ", " +
					level.getName() + ") into table " + LEVELS_TABLE);
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Error while insert in table " + LEVELS_TABLE);
			return false;
		} finally {
			closeConnection();
		}
		
		return true;
	}
	
	private long[] extractLevelIds(ArrayList<AchievementLevel> levels) {
		long[] ids = new long[levels.size()];
		for (int i = 0; i < levels.size(); i++) {
			ids[i] = levels.get(i).getId();
		}
		return ids;
	}
	
	private long[] extractLevelItemsIds(List<AchievementLevelItem> items) {
		long[] ids = new long[items.size()];
		for (int i = 0; i < items.size(); i++) {
			ids[i] = items.get(i).getId();
		}
		return ids;
	}
	
	public ArrayList<AchievementLevel> getLevelsByLevelItems(List<AchievementLevelItem> items) {
		ArrayList<AchievementLevel> levels = new ArrayList<AchievementLevel>();
		
		try {
			openConnection();			
			String query = "SELECT * FROM " + LEVEL_ITEM_MAPPING_TABLE + " WHERE LEVEL_ITEM_ID IN " 
											+ buildInClauseForIds(extractLevelItemsIds(items));
			
			Cursor c = generatorDB.rawQuery(query, null);
			
			int levelIdColumn = c.getColumnIndex("LEVEL_ID");
			
			if (c.moveToFirst()) {
				do {
					int levelId = c.getInt(levelIdColumn);
					AchievementLevel level = getLevelById(levelId);
					if (level != null && !levels.contains(level)) {
						levels.add(level);
					}
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + LEVEL_ITEM_MAPPING_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		
		return levels;
	}

	public ArrayList<AchievementLevelItem> getLevelItemsForId(int levelId) {
		ArrayList<AchievementLevelItem> result = new ArrayList<AchievementLevelItem>();
		
		int levelItemId;
		try {
			openConnection();
			
			String query = "SELECT * FROM " + LEVEL_ITEM_MAPPING_TABLE + " WHERE LEVEL_ID = ?";
			
			Cursor c = generatorDB.rawQuery(query, new String[]{ Integer.toString(levelId) });
			
			int levelIdColumn = c.getColumnIndex("LEVEL_ITEM_ID");
			
			if (c.moveToFirst()) {
				do {
					levelItemId = c.getInt(levelIdColumn);
					AchievementLevelItem levelItem = getLevelItemById(levelItemId);
					if (levelItem != null) {
						result.add(levelItem);
					}
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + LEVEL_MAPPING_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		
		return result;
	}

	private AchievementLevelItem getLevelItemById(int levelItemId) {
		AchievementLevelItem achLevelItem = null;
		
		String query = "SELECT * FROM " + LEVEL_ITEMS_TABLE + " WHERE id = ?";
		try {
			Cursor c = generatorDB.rawQuery(query, new String[]{ Integer.toString(levelItemId) });
			
			int goalColumn = c.getColumnIndex("GOAL");
			int activityIdColumn = c.getColumnIndex("ACTIVITY_ID");

			if (c.moveToFirst()) {
				do {
					int goal = c.getInt(goalColumn);
					int activityId = c.getInt(activityIdColumn);
					achLevelItem = new AchievementLevelItem(
							levelItemId, getActivityById(activityId), goal, goal, false);
					achLevelItem.updateRemaining(getTimeSpentForActivity(activityId));
				} while (c.moveToNext());
			}
			c.close();
		} catch (Exception e) {
			Log.e(getClass().getSimpleName(), "Cannot get level for id = " + levelItemId, e);
		}
		return achLevelItem;
	}

	public boolean updateLevelItem(AchievementLevelItem levelItem) {
		try {
    		openConnection();
    		ContentValues values = getValuesFromLevelItem(levelItem, true);
    		openConnection();
    		generatorDB.update(LEVEL_ITEMS_TABLE, values, "id=?", 
    				new String[]{ Integer.toString(levelItem.getId()) });
    		Log.d(getClass().getSimpleName(), "Successfully updated to (" + levelItem.getActivityItem().getName() + ", " +
					levelItem.getActivityItem().getCategory() + ") in table " + LEVEL_ITEMS_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot update to (" + levelItem.getActivityItem().getName() + ", " +
					levelItem.getActivityItem().getCategory() + ") in table " + LEVEL_ITEMS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public boolean deleteLevelItem(AchievementLevelItem levelItem, int levelId) {
		try {
    		openConnection();
    		generatorDB.delete(LEVEL_ITEMS_TABLE, "id=?", 
    				new String[]{ Integer.toString(levelItem.getId()) });
    		generatorDB.delete(LEVEL_ITEM_MAPPING_TABLE, "LEVEL_ITEM_ID = ? AND LEVEL_ID = ?", 
    				new String[]{ Integer.toString(levelItem.getId()), Integer.toString(levelId) });
    		Log.d(getClass().getSimpleName(), "Successfully deleted (" + levelItem.getActivityItem().getName() 
    				+ ", " + levelItem.getActivityItem().getCategory() + ") from table " + LEVELS_TABLE);
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot delete (" + levelItem.getActivityItem().getName() 
    				+ ", " + levelItem.getActivityItem().getCategory() + ") from table " + LEVELS_TABLE, e);
    		return false;
    	} finally {
    		closeConnection();
    	}
    	return true;
	}

	public boolean insertLevelItem(AchievementLevelItem levelItem, int levelId) {
		try {
			openConnection();
			int id = (int) generatorDB.insertOrThrow(LEVEL_ITEMS_TABLE, null, getValuesFromLevelItem(levelItem, false));
			levelItem.setId(id);
			ContentValues values = new ContentValues();
			values.put("LEVEL_ITEM_ID", levelItem.getId());
			values.put("LEVEL_ID", levelId);
			//openConnection();
			generatorDB.insertOrThrow(LEVEL_ITEM_MAPPING_TABLE, null, values);
			Log.d(getClass().getSimpleName(), "Successfully inserted (" + levelItem.getActivityItem().getName() + ", " +
					levelItem.getActivityItem().getCategory() + ") into table " + LEVEL_ITEMS_TABLE);
		} catch (Exception e) {
			Log.e(getClass().getSimpleName(), "Error while insert in table " + LEVEL_ITEMS_TABLE);
			return false;
		} finally {
			closeConnection();
		}
		
		return true;
	}

	private ActivityItem getActivityById(int id) {
		ActivityItem item = new ActivityItem("", "", "");
		
		String query = "SELECT * FROM " + ACTIVITY_ITEMS_TABLE + " WHERE id = ?";
		try {
			Cursor c = generatorDB.rawQuery(query, new String[]{ Integer.toString(id) });
			
			int nameColumn = c.getColumnIndex("NAME");
			int categoryColumn = c.getColumnIndex("CATEGORY");
			int descColumn = c.getColumnIndex("DESCRIPTION");
			
			if (c.moveToFirst()) {
				item = new ActivityItem(id, c.getString(nameColumn), c.getString(descColumn), c.getString(categoryColumn));
			}
			c.close();
		} catch (Exception e) {
			Log.e(getClass().getSimpleName(), "Cannot get activity item for id = " + id, e);
		}
		return item;
	}
	
	public ArrayList<AchievementLevelItem> updateNotCompletedLevelItems() {
		ArrayList<AchievementLevelItem> items = new ArrayList<AchievementLevelItem>();
		try {
			openConnection();
			
			String query = "SELECT * FROM " + LEVEL_ITEMS_TABLE + " WHERE IS_COMPLETED = 0";
			
			Cursor c = generatorDB.rawQuery(query, null);
			AchievementLevelItem achLevelItem = null;
			int activityIdColumn = c.getColumnIndex("ACTIVITY_ID");
			int goalColumn = c.getColumnIndex("GOAL");
			int levelItemIdColumn = c.getColumnIndex("id");
			
			if (c.moveToFirst()) {
				do {
					int goal = c.getInt(goalColumn);
					int activityId = c.getInt(activityIdColumn);
					int levelItemId = c.getInt(levelItemIdColumn);
					achLevelItem = new AchievementLevelItem(
							levelItemId, getActivityById(activityId), goal, goal, false);
					achLevelItem.updateRemaining(getTimeSpentForActivity(activityId));
					items.add(achLevelItem);
				} while (c.moveToNext());
			}
			c.close();
		} catch(Exception e) {    
			Log.e(getClass().getSimpleName(), "Records for table " + LEVEL_ITEMS_TABLE 
					+ " cannot be retrieved from the database.", e);
		} finally {
			closeConnection();
		}
		return items;
	}
	
	public boolean trackMood(TrackedMood trackedMood) {
		
		boolean success = false;
		try {
			openConnection();
			generatorDB.insertOrThrow(MOOD_TABLE, null, getValuesFromMood(trackedMood));
				Log.d(getClass().getSimpleName(), "Successfully inserted (" + trackedMood.getMoodCode() + ", " +
						trackedMood.getStartDate() + ", " + trackedMood.getEndDate() + ") into table " + MOOD_TABLE);
				success = true;
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot track time: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return success;
	}

	public boolean updateMood(TrackedMood mood) {
		
		boolean success = true;
		try {    		
    		TrackedMood moodToUpdate = getCurrentMood();
    		
    		openConnection();

    		if(moodToUpdate != null) {
    			int count = generatorDB.update(MOOD_TABLE, getValuesForCurrentMood("END"), "id = ?", 
	    				new String[]{Long.toString(moodToUpdate.getId())});
	    		if (count == 0) {
	    			success = false;
	    		}
    		}
    		TrackedMood newMood = new TrackedMood(mood.getMoodCode(), mood.getStartDate(), mood.getEndDate());
    		trackMood(newMood);
    		
    	} catch(Exception e) {
    		Log.e(getClass().getSimpleName(), "Cannot update row", e);
    		success = false;
    	} finally {
    		closeConnection();
    	}
    	return success;
	}

	private TrackedMood getCurrentMood() {
		
		TrackedMood currentMood = null;
		try {
    		openConnection();
    		String query = "SELECT * FROM TBL_MOOD WHERE END IS NULL";
    		
    		Cursor c = generatorDB.rawQuery(query, null);
    		
    		int idColumn = c.getColumnIndex("id");
    		int moodCodeColumn = c.getColumnIndex("MOOD_CODE");
    		int startDateColumn = c.getColumnIndex("START");
    		
			int trackedMoodId = -1;
			if (c.moveToFirst()) {
				trackedMoodId = c.getInt(idColumn);
			}
			if(trackedMoodId != -1) {
				currentMood =  new TrackedMood(c.getInt(moodCodeColumn),
						new Date(c.getString(startDateColumn)), null);
				currentMood.setId(c.getInt(idColumn));
			}
			c.close();
    	} catch(Exception e) {
    		e.printStackTrace();
    	} finally {
    		closeConnection();
    	}
		return currentMood;
	}

	public int getCurrentMoodCode() {
		
		TrackedMood m = getCurrentMood();
		if(m == null)
			return Mood.OK.getCode();
		else 
			return m.getMoodCode();
	}

	@SuppressWarnings("unused")
	private TrackedMood getTrackedMoodByID(long id) {
		
		TrackedMood item = null;
		
		String query = "SELECT * FROM " + MOOD_TABLE + " WHERE id = ?";
		try {
			Cursor c = generatorDB.rawQuery(query, new String[]{ Long.toString(id) });
			
			int moodColumn = c.getColumnIndex("MOOD_CODE");
			int startColumn = c.getColumnIndex("START");
			
			if (c.moveToFirst()) {
				item = new TrackedMood(c.getInt(moodColumn), new Date(c.getString(startColumn)), null);
			}
			c.close();
		} catch (Exception e) {
			Log.e(getClass().getSimpleName(), "Cannot get activity item for id = " + id, e);
		}
		return item;
	}
	
	public List<ActivitiesPerCategoryReportItem> getActivitiesPieChart(String category) {
		
		List<ActivitiesPerCategoryReportItem> reportItems = new ArrayList<ActivitiesPerCategoryReportItem>();
		
		try {
			openConnection();
			
			String allActivitiesOfCategory = "SELECT NAME, SUM (TIME_SPENT) FROM " + TRACK_TABLE + 
				" JOIN " + ACTIVITY_ITEMS_TABLE + " ON TBL_TRACK.TIMEACTION_ID=TBL_TIMEACTIONS.id" +
				" WHERE CATEGORY='" + category + "' " + 
				" group by NAME;";
			
			Cursor c = generatorDB.rawQuery(allActivitiesOfCategory, null);
					
			String activityName = null;
			double spenTime;
			
			if (c.moveToFirst()) {
				do {
					activityName = c.getString(0);
					spenTime = c.getInt(1);
					reportItems.add(new ActivitiesPerCategoryReportItem(activityName, spenTime));
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		
		return reportItems;
	}

	public void updateTrackRows() {
		for (TrackLog t : getAllTracks()) {
			updateTrackRow(t);
		}
	}

	public List<CategoriesReportItem> getCategoriesPieChart(String period) {
		
		List<CategoriesReportItem> reportItems = new ArrayList<CategoriesReportItem>();
		
		try {
			openConnection();
			
			String allActivitiesOfCategory = "SELECT TBL_CATEGORY.NAME, SUM (TIME_SPENT) FROM " + TRACK_TABLE + 
				" JOIN " + ACTIVITY_ITEMS_TABLE + " ON TBL_TRACK.TIMEACTION_ID=TBL_TIMEACTIONS.id" + 
				" JOIN " + CATEGORY_TABLE + " ON TBL_CATEGORY.NAME=TBL_TIMEACTIONS.CATEGORY" +
				" group by TBL_CATEGORY.NAME;";
			
			Cursor c = generatorDB.rawQuery(allActivitiesOfCategory, null);
					
			String categoryName = null;
			double spenTime;
			
			if (c.moveToFirst()) {
				do {
					categoryName = c.getString(0);
					spenTime = c.getInt(1);
					reportItems.add(new CategoriesReportItem(categoryName, spenTime));
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		
		return reportItems;
	}
	
	public List<MoodReportItem> getMoodLog() {
		
		ArrayList<MoodReportItem> items = new ArrayList<MoodReportItem>();
		
		int moodCode;
		String startDateStr;
		String endDateStr;
		double time;
		try {
			openConnection();
			String query = "SELECT * FROM " + MOOD_TABLE;
			
			Cursor c = generatorDB.rawQuery(query, null);
			
			int moodColumn = c.getColumnIndex("MOOD_CODE");
			int startColumn = c.getColumnIndex("START");
			int endColumn = c.getColumnIndex("END");
			
			if (c.moveToFirst()) {
				do {
					moodCode = c.getInt(moodColumn);
					startDateStr = c.getString(startColumn);
					endDateStr = c.getString(endColumn);
					
					Date start = new Date(startDateStr);
					Date end = new Date();
					if(endDateStr != null) {
						end = new Date(endDateStr);
					}
					
					time = (end.getTime() - start.getTime())/60000;
					
					items.add(new MoodReportItem(moodCode, start, end, time));
				} while (c.moveToNext());
			}
			c.close();
		} catch (SQLException e) {
			Log.e(getClass().getSimpleName(), "Cannot get all ongoing activities: " + e.getMessage(), e);
		} finally {
			closeConnection();
		}
		return items;
	}
}
